package exec;

import java.sql.*;
import java.util.ArrayList;

import database.DbConnection;
import database.DbNames;

import parser.Constants;
import parser.IupacParser;

public class DbUpdater {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
		
		
		String selectedDb = DbNames.glycosuite; //glycosuite; //sugarbind12
		
		ArrayList<String> errIds = new ArrayList<String>();
		IupacParser p= new IupacParser();
		
		long beginCompleteUpdate = System.nanoTime();
		long endCompleteUpdate;
		int rowsSelect = 0;
		int rowsUpdate = 0;
		int rowsUpdated = 0;
		
		String structureId = null;
		String structure = null;
		String glycanType = null;
//		String ctSequence=null;

//		try 
//		{
//			Class.forName("org.postgresql.Driver");
//			System.out.println("Class.forName(org.postgresql.Driver); : Driver loaded!");
//		} 
//		catch (ClassNotFoundException e) 
//		{
//			System.err.println(e.getMessage());
//		}

		
		DbConnection connect = new DbConnection();
		connect.loadDriver();
		
		String url = connect.getUrl(selectedDb);
		String querySelect = "";
		String queryUpdate = "";
		
		
		
		if(selectedDb.equals(DbNames.glycosuite))
		{
			querySelect = "SELECT "+Constants.doubleQuote+"STRUCTURE_ID"+Constants.doubleQuote+", "+Constants.doubleQuote+"GLYCAN_ST"+Constants.doubleQuote+", "+Constants.doubleQuote+"GLYCAN_TYPE"+Constants.doubleQuote+" from glycodb."+Constants.doubleQuote+"STRUCTURE"+Constants.doubleQuote+";";
		}
		else if(selectedDb.equals(DbNames.sugarbind12))
		{
			querySelect = "SELECT ligand_id, std_nomenclature, null from nomenclature;";
		}
		else
		{
			System.err.println("No database selected!");
		}
		
		try 
		{
			Connection conn = DriverManager.getConnection(url);

//			String querySelectGS = "SELECT "+Constants.doubleQuote+"STRUCTURE_ID"+Constants.doubleQuote+", "+Constants.doubleQuote+"GLYCAN_ST"+Constants.doubleQuote+", "+Constants.doubleQuote+"GLYCAN_TYPE"+Constants.doubleQuote+" from glycodb."+Constants.doubleQuote+"STRUCTURE"+Constants.doubleQuote+";";
//			String querySelectSB = "SELECT ligand_id, std_nomenclature, null from nomenclature;";
			
			Statement st_Select = conn.createStatement();
			ResultSet rs = st_Select.executeQuery(querySelect);
			/*
			 * GS
			SELECT * from glycodb."STRUCTURE"
			ALTER TABLE glycodb."STRUCTURE" ADD COLUMN "GLYCAN_ST_CT" character varying(1500);
			ALTER TABLE glycodb."STRUCTURE" ADD COLUMN "GLYCAN_ST_CT_MOD_TIME" character varying(150);
			
			* SB
			SELECT * from nomenclature
			ALTER TABLE nomenclature ADD COLUMN GLYCAN_ST_CT character varying(1500);
			ALTER TABLE nomenclature ADD COLUMN GLYCAN_ST_CT_MOD_TIME character varying(150);
			*/

			while (rs.next()) {
				structureId = rs.getString(1);
				structure = rs.getString(2);
				glycanType = rs.getString(3);
				
				String ctSequence=null;
				
				rowsSelect++;
			    System.out.print(rowsSelect + "/ ID returned ");
			    
			    System.out.println(structureId);
			    System.out.println(structure);
			    
			    long begin = System.nanoTime();
				
				try
				{
					p.setIupacSequence(structure);
					p.setGlycanType(glycanType);
					
					p.getCtTree(p.parse());
					ctSequence=p.getCtSequence();
					
					System.out.println("");
					System.out.println("CT sequence : ");
					System.out.println(ctSequence);
				}
				catch(Exception ex)
				{
					System.err.println("Problem parsing the sequence");
					System.err.println(ex.getMessage());
					errIds.add(structureId+";Problem parsing the sequence:"+ex.getMessage()+'\n');
				}
				long end = System.nanoTime();
				
				System.out.println("");
				System.out.println("Process time [s] : " + (end-begin)*Math.pow(10, -9));
			    
			    if(ctSequence!=null)
			    {	
			    rowsUpdate++;
			    
			    
				    if(selectedDb.equals(DbNames.glycosuite))
					{
						queryUpdate = "UPDATE glycodb."+Constants.doubleQuote+"STRUCTURE"+Constants.doubleQuote
							+ " SET "+Constants.doubleQuote+"GLYCAN_ST_CT"+Constants.doubleQuote+"="+Constants.singleQuote+ctSequence+Constants.singleQuote
							+ ","+Constants.doubleQuote+"GLYCAN_ST_CT_MOD_TIME"+Constants.doubleQuote+"=(SELECT LOCALTIMESTAMP)"
							+ " WHERE "+Constants.doubleQuote+"STRUCTURE_ID"+Constants.doubleQuote+" = "+structureId
							+ " AND ("+Constants.doubleQuote+"GLYCAN_ST_CT"+Constants.doubleQuote + "!= "+Constants.singleQuote+ctSequence+Constants.singleQuote
							+ " OR "+Constants.doubleQuote+"GLYCAN_ST_CT"+Constants.doubleQuote + " is null)"
							+ ";";
					}
					else if(selectedDb.equals(DbNames.sugarbind12))
					{
						queryUpdate = "UPDATE nomenclature"
							+ " SET GLYCAN_ST_CT="+Constants.singleQuote+ctSequence+Constants.singleQuote
							+ ",GLYCAN_ST_CT_MOD_TIME=(SELECT LOCALTIMESTAMP)"
							+ " WHERE LIGAND_ID = "+structureId
							+ " AND std_nomenclature = "+Constants.singleQuote+ structure +Constants.singleQuote
							+ " AND (GLYCAN_ST_CT!= "+Constants.singleQuote+ ctSequence +Constants.singleQuote
							+ " OR GLYCAN_ST_CT is null)"
							+ ";";
					}
					else
					{
						System.err.println("No database selected!");
					}
				    /*update only modified glycoCT code*/ 
	//			    String queryUpdateGS = 
	//			    
	//			    String queryUpdateSB = 
				    
				    PreparedStatement st_Update = conn.prepareStatement(queryUpdate);
				    rowsUpdated = st_Update.executeUpdate();
				    //System.out.println(rowsUpdated + " rows updated");
				    st_Update.close();
				    
//				    System.out.println(st_Update);
			    }
			}
			rs.close();
			st_Select.close();
		} 
		catch (SQLException e) 
		{
			System.err.println(e.getMessage());
			errIds.add(structureId+";"+e.getMessage()+'\n');
		}
		catch (Exception ex) 
		{
			System.err.println(ex.getMessage());
			errIds.add(structureId+";"+ex.getMessage()+'\n');
		}
		
		endCompleteUpdate = System.nanoTime();
		
		System.out.println("");
		System.out.println("Process time [s] : " + (endCompleteUpdate-beginCompleteUpdate)*Math.pow(10, -9));
		System.out.println("Rows selected : " + rowsSelect);
		System.out.println("Rows translatable : " + rowsUpdate);
		System.out.println("Rows updated : " + rowsUpdated);
		
		System.err.println("Errors : " + '\n' + errIds);
		}

}
